set echo off
--------------------------------------------------------------------------------
-- @name: prof_callstack
-- @author: dion cho
-- @note: troubleshoot using oradebug callstack dump
-- @usage: @prof_callstack <sid> <sleep> <sampleing#>
--------------------------------------------------------------------------------
define __SID = &1
define __SLEEP = &2
define __SAMPLE = &3

col spid new_value v_spid

select spid
from v$process
where addr = (select paddr from v$session where sid = &__SID);

oradebug setospid &v_spid

@make_callstack &__SLEEP &__SAMPLE

@oradebug_callstack

/*
-- you must create this table!
drop table callstack_temp purge;

create global temporary table
callstack_temp(seq number, idx number, fname varchar2(20));

*/

declare
    b_start        boolean := false;
    b_first        boolean := false;
    fname            varchar2(20);
    ftype            varchar2(10);
    seq                number := 1;
    idx                number := 1;
begin
    for r in (select * from table(ukja.get_trace_file1(&__SID))) loop
        if instr(r.column_value, 'Call Stack Trace') > 0 then
            b_start := true;
            b_first := true;
        end if;
        
        if instr(r.column_value, 'End of Call Stack Trace') >0 then
            seq := seq+1;
            idx := 1;
            b_start := false;
        end if;
        
        if b_start = true and b_first = false then
            fname := rtrim(substr(r.column_value, 1, 20));
            ftype := rtrim(substr(r.column_value, 22, 8));
            if upper(ftype) like 'CALL%' or upper(ftype) = 'VIRTUAL' then
                dbms_output.put_line(fname);
                insert into callstack_temp values(seq, idx, fname);
                idx := idx + 1;
            end if;
        end if;
        
        if b_start = true then
            b_first := false;
        end if;
        
    end loop;
end;
/

spool callstack_result.txt

set line 80

col sample# new_value sample_count

select
    count(distinct seq) as sample#,
    count(*) as call#,
    max(idx) as max_depth
from callstack_temp
;

-- unique callstack
select * from (
    select fname, count(*)
    from callstack_temp
    group by fname
    order by 2 desc
) where rownum <= 50
;

select
    seq, idx, fname
from callstack_temp
where seq = 1
order by seq asc, idx desc
;

-- delete too long call stack
define MAX_LENGTH = 100

delete from callstack_temp
where idx >= &MAX_LENGTH;

col ratio format 99.999

select
    count(*) as hit#,
    100*(count(*)/&sample_count) as ratio,
    stack
from
(
    select
        seq,
        ltrim(sys_connect_by_path(fname,'->'),',') stack
    from (
        select
            seq,
            fname,
            idx,
            row_number() over (partition by seq order by idx desc) rn,
            count(*) over (partition by seq) cnt
        from callstack_temp
    )
    where level = cnt
    start with rn = 1
    connect by prior seq = seq and prior rn = rn-1
)
group by stack
order by 1 desc
;

spool off
@ed callstack_result.txt

set line 80

delete from callstack_temp;

set echo on
